{ "cells": [ { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "# IIoT data\n", "\n", "## Try me\n", "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/ffraile/computer_science_tutorials/blob/main/source/Databases/Extra%20Exercises/IIoT%20data%20(solved).ipynb)[![Binder](https://mybinder.org/badge_logo.svg)](https://mybinder.org/v2/gh/ffraile/computer_science_tutorials/main?labpath=source%2FDatabases%2FExtra%20Exercises%2FIIoT%20data%20(solved).ipynb)\n", "\n", "## Problem definition\n", "Your company is involved in a digital transformation project and you would like to design a database to store readings from sensors installed in Industrial Internet of Things (IIoT) devices.\n", "For now, your organisation just wants to design a database that it is able to manage data from different types of devices and sensors. \n", "There are different device models, so, you want to store the device type information (e.g name and description). Also, there are different types of sensors, so you want to store the information about the sensor type (name, measurement unit, upper range and lower range), for instance, a temperature sensor which measurement unit is celsius degrees and can measure temperatures from -20 degrees to 60 degrees. \n", "You will have a network of IIoT devices, and you would like to know what is the location of each device, and the current status (either OK or ERROR). \n", "Also, each IIoT device will be connected to a set of sensors, each sensor will be of a specific type, connected to a specific device and will have a status (either OK or ERROR). \n", "Finally, you want to store sensor readings, knowing which sensor generated the reading, the timestamp where the measurement reading was created and its value. (for instance, a temperature sensor registered a temperature of 27.5 degrees on the 2021/12/10 at 17:12:00). \n", "Design a database model to support this use case. " ] }, { "cell_type": "markdown", "source": [ "## Database Design\n", "The following diagram shows a possible solution for the database: \n", "\n", "![iiot model](https://github.com/ffraile/database_tutorials/blob/main/Extra%20Exercises/img/iiot.png?raw=true)\n", "\n", "The key is to define two tables for the device type and the sensor type where we can store all generic information. This way, the database will scale nicely even if we install many devices equipped with many sensors. Thus, each device will have a type ([1:N] relationship between device_types and devices), and each sensor will have a type ([1:N] relationship between sensor_types and sensors). Finally, each sensor reading will be related to a sensor ([1:N] relationship between sensors and sensor_readings).\n", "\n", "## Try it yourself\n", "The file [IIot MySQL test database](https://github.com/ffraile/computer_science_tutorials/blob/main/source/Databases/test_databases/IIoT%20database.sql) contains an implementation of this model, plus some sample data useful to build queries on timestamped data.\n", "\n", "## Data analysis queries\n", "\n", "Try to build the following ```SELECT``` queries to analyse the data.\n", "\n", "1. Write a ```SELECT```query to know what is the average value of every sensor\n", "\n", "2. Write a ```SELECT``` query to know the date of the most recent sensor reading for every sensor\n", "\n", "3. Write a ```SELECT``` query to show the different sensors of device with ID 1\n", "\n", "4. Write a ```SELECT``` query to show the number of measurements, average value, maximum value and minimum value of the different sensors of device with ID 1\n", "\n", "## Data analysis queries solved\n", "\n", "1. Write a ```SELECT```query to know what is the average value of every sensor\n", "\n", "The following query uses a ```GROUP``` clause to group the reading values by sensor:\n", "\n", "```mysql\n", "SELECT sensor_fk, AVG(reading_value)\n", "\tFROM sensor_readings\n", " GROUP BY sensor_fk;\n", "```\n", "\n", "2. Write a ```SELECT``` query to know the date of the most recent sensor reading for every sensor\n", "\n", "The following query uses the ```MAX()``` aggregated function to find out the date of the most recent sensor reading (since the latest reading of every sensor will have the greatest date)\n", "\n", "```mysql\n", "SELECT sensor_fk, MAX(created)\n", "\tFROM sensor_readings\n", " GROUP BY sensor_fk;\n", "```\n", "\n", "3. Write a ```SELECT``` query to show the different sensors of device with ID 1\n", "\n", "We could just use a ```WHERE``` clause to select just the sensors with ```device_fk``` equal to 1. The following alternative solution uses a join clause so that we could add more information of the device if needed.\n", "\n", "```mysql\n", "SELECT d.device_id, s.sensor_id\n", "\tFROM devices d\n", " LEFT JOIN sensors s ON (s.device_fk = d.device_id)\n", " WHERE device_id = 1;\n", "```\n", "4. Write a ```SELECT``` query to show the number of measurements, average value, maximum value and minimum value of the different sensors of device with ID 1\n", "\n", "We extend on solutions of previous queries to build the following query where the measurements are grouped by ```sensor_id```\n", "\n", "```mysql\n", "SELECT d.device_id,\n", "\t\ts.sensor_id,\n", " COUNT(reading_value) AS total_measurements,\n", " AVG(reading_value) AS average,\n", " MAX(reading_value) AS maximum,\n", " MIN(reading_value) AS minimum\n", "\tFROM devices d\n", " LEFT JOIN sensors s ON (s.device_fk = d.device_id)\n", " LEFT JOIN sensor_readings sr ON (sr.sensor_fk = s.sensor_id)\n", " WHERE device_id = 1\n", " GROUP BY sensor_id;\n", "```" ], "metadata": { "collapsed": false } } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.6" }, "pycharm": { "stem_cell": { "cell_type": "raw", "source": [], "metadata": { "collapsed": false } } } }, "nbformat": 4, "nbformat_minor": 0 }